package com.zdgcc.site.db;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import com.zdgcc.site.domain.Member;

public class DBUtil {

	private static String url;
	private static String user;
	private static String password;
	private static String driver;

	// 注册驱动

	static {
		try {
			Properties prop = new Properties();
			InputStream in = DBUtil.class.getClassLoader().getResourceAsStream("connConfig.properties");
			prop.load(in);
			url = prop.getProperty("url");
			user = prop.getProperty("user");
			password = prop.getProperty("password");
			driver = prop.getProperty("driver");

			Class.forName(driver);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 从数据源中得到连接
	 * 
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnection() throws Exception {
		return DriverManager.getConnection(url, user, password);
	}

	/**
	 * 从给定的数据源泉中得到连接
	 * 
	 * @param dataSourceName
	 * @return
	 * @throws Exception
	 */
	// public static Connection getConnection(String dataSourceName)
	// throws Exception {
	// Context cont = new InitialContext();
	// ds = (DataSource) cont.lookup(dataSourceName);
	// return ds.getConnection();
	// }

	/**
	 * 关闭连接
	 * 
	 * @param conn
	 * @throws Exception
	 */
	public static void closeConnection(Connection conn) throws Exception {
		if (conn != null) {
			conn.close();
		}
	}

	/**
	 * 执行除查询外静态SQL语句
	 * 
	 * @param conn
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public static boolean execute(Connection conn, String sql) throws SQLException {
		PreparedStatement preState = conn.prepareStatement(sql);
		return preState.execute();
	}

	/**
	 * 执行查询得到ResultSet
	 * 
	 * @param conn
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	private static ResultSet executeQuery(Connection conn, String sql) throws SQLException {
		PreparedStatement preState = conn.prepareStatement(sql);
		return preState.executeQuery();
	}

	/**
	 * 根据SQL语句得到一个对象LIST
	 * 
	 * @param <T>
	 * @param clazz
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("rawtypes")
	public static <T> List<T> getObjectList(Class<T> clazz, Connection conn, String sql) throws Exception {
		List<T> list = new ArrayList<T>();
		ResultSet rs = executeQuery(conn, sql);
		ResultSetMetaData meta = rs.getMetaData();
		int colCount = meta.getColumnCount();
		// 业务对象的属性数组
		Field[] fields = clazz.getDeclaredFields();
		// colCount = colCount>fields.length?fields.length:colCount;
		Field f = null;
		while (rs.next()) {// 对每一条记录进行操作
			T obj = clazz.newInstance();// 构造业务对象实体
			// 将每一个字段取出进行赋值
			for (int i = 1; i <= colCount; i++) {
				String value = rs.getObject(i) == null ? rs.getString(i) : String.valueOf(rs.getObject(i));
				if (value != null) {
					// 寻找该列对应的对象属性
					for (int j = 0; j < fields.length; j++) {
						f = fields[j];
						// 如果匹配进行赋值
						if (f.getName().equalsIgnoreCase(meta.getColumnName(i))) {
							boolean flag = f.isAccessible();
							f.setAccessible(true);
							Class cls = f.getType();
							f.set(obj, ValueOfRequest.valueOf(cls, value));
							f.setAccessible(flag);
							break;
						}
					}
				}
			}
			list.add(obj);
		}
		return list;
	}

	/**
	 * 根据SQL语句得到一个对象数组LIST
	 * 
	 * @param <T>
	 * @param clazz
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	// public static List getObjectArrayList(Connection conn, String sql)
	// throws Exception {
	// List list = new ArrayList();
	// ResultSet rs = executeQuery(conn, sql);
	// ResultSetMetaData rsmd = rs.getMetaData();
	// int colCount = rsmd.getColumnCount();
	// while (rs.next()) {
	// Object[] obj = new Object[colCount];
	// for (int i = 0; i < colCount; i++) {
	// obj[i] = rs.getObject(i + 1);
	// }
	// list.add(obj);
	// }
	// return list;
	// }

	/**
	 * 根据SQL语句得到一个对象
	 * 
	 * @param <T>
	 * @param clazz
	 * @param conn
	 * @param sql
	 * @return
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 */
	@SuppressWarnings("rawtypes")
	public static <T> Object getObject(Class<T> clazz, Connection conn, String sql) throws SQLException, InstantiationException,
			IllegalAccessException {
		T obj = null;
		ResultSet rs = executeQuery(conn, sql);
		Field[] fields = clazz.getDeclaredFields();
		Field f = null;
		if (rs.next()) {
			ResultSetMetaData meta = rs.getMetaData();
			int colCount = meta.getColumnCount();
			obj = clazz.newInstance();// 构造业务对象实体
			// 将每一个字段取出进行赋值
			for (int i = 1; i <= colCount; i++) {
				String value = rs.getString(i);
				if (value != null) {
					// 寻找该列对应的对象属性
					for (int j = 0; j < fields.length; j++) {
						f = fields[j];
						// 如果匹配进行赋值
						if (f.getName().equalsIgnoreCase(meta.getColumnName(i))) {
							boolean flag = f.isAccessible();
							f.setAccessible(true);
							Class cls = f.getType();
							f.set(obj, ValueOfRequest.valueOf(cls, value));
							f.setAccessible(flag);
							break;
						}
					}
				}
			}
		}
		return obj;
	}

	/**
	 * 根据SQL语句得到一个对象数组
	 * 
	 * @param conn
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	// public static Object[] getObjectArray(Connection conn, String sql)
	// throws SQLException {
	// Object[] obj = null;
	// ResultSet rs = executeQuery(conn, sql);
	// if (rs.next()) {
	// ResultSetMetaData rsmd = rs.getMetaData();
	// int colCount = rsmd.getColumnCount();
	// obj = new Object[colCount];
	// for (int i = 0; i < colCount; i++) {
	// obj[i] = rs.getObject(i + 1);
	// }
	// }
	// return obj;
	// }

	/**
	 * 得到单个的数据
	 * 
	 * @param conn
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	// public static Object getOne(Connection conn, String sql)
	// throws SQLException {
	// Object obj = null;
	// Object[] o = getObjectArray(conn, sql);
	// if (o != null) {
	// obj = o[0];
	// }
	// return obj;
	// }

	/**
	 * 批量执行SQL，不成功回滚，已经关闭连接 返回true表示成功，false失败
	 * 
	 * @param conn
	 * @param sql
	 * @return
	 */
	public static boolean statementBatch(Connection conn, String[] sql) {
		boolean b = true;
		try {
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			for (int i = 0; i < sql.length; i++) {
				stmt.addBatch(sql[i]);
			}
			stmt.executeBatch();
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				b = false;
			}
			e.printStackTrace();
			b = false;
		} finally {
			try {
				closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				b = false;
			}
		}
		return b;
	}

	/**
	 * 检查要查询的是否存在
	 * 
	 * @param conn
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public static boolean isExist(Connection conn, String sql) throws SQLException {
		boolean b = false;
		ResultSet rs = executeQuery(conn, sql);
		if (rs.next()) {
			b = true;
		}
		return b;
	}

	public static void main(String[] strs) {
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			System.out.println(conn == null ? "no" : "yes");
			String sql = "SELECT M.MEMBER_ID AS ID, M.MEMBER_NAME AS NAME, M.MEMBER_PWD AS PWD " + "FROM TECH_T_MEMBER_BASE M "
					+ "ORDER BY M.MEMBER_REG_DATE DESC";
			List<Member> memberList = DBUtil.getObjectList(Member.class, conn, sql);
			for (Member member : memberList) {
				System.out.println("id:" + member.getId());
			}
			// String id = Util.getMD5ID(3);
			// String pwd = Util.getMD5("1");
			// String insert =
			// "insert into TECH_T_MEMBER_BASE(MEMBER_ID,MEMBER_NAME,MEMBER_PWD) "
			// +"values('"+id+"','admin','"+pwd+"')";
			//
			// boolean b = DBUtil.execute(conn, insert);
			//
			// System.out.println(b==true?"ok":"no");

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

}
